package com.njzxw.fs.sql;

import com.njzxw.fs.commonutil.util.StringHelper;
import com.njzxw.fs.sql.repository.BackReq;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;
import org.testng.annotations.Test;

import javax.annotation.PostConstruct;
import java.io.*;
import java.nio.channels.FileChannel;
import java.util.Arrays;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

@Service
public class BackMysql {

    public org.slf4j.Logger logger = LoggerFactory.getLogger(this.getClass());

    @Autowired
    private BackReq backReq;

    @Value("${tableName}")
    private String[] tableName;

    @Value("${funname}")
    private String[] funname;

    @Value("${insertTableName}")
    private String[] insertTableName;

    String fgf = "#-#-#-#-#-#-#-#-#-#-#-#-#-#-#-#-#-#-#-#-#-#-#-#-#-#-#-#";

    @PostConstruct
    public void execute(){

        String version = backReq.version();
        String fileName = "create_" + version + ".sql";

        String path_ = null;
        try {
            path_ = java.net.URLDecoder.decode(this.getClass().getResource("/").getPath(),"utf-8");
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }
        String path = path_.substring(0, path_.indexOf("/target/") + 1) + "src/sqlBack/";

        logger.debug("文件存储路径："+path);
        File filePath = new File(path);
        if (!filePath.exists()) {
            filePath.mkdirs();
        }
        File file = new File(path + fileName);

        if (file.exists()) {
            file.delete();
        }

        try {
            file.createNewFile();

            createTableSql(tableName, file);
            createFunctionSql(funname, file);

            createInsertSql();

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            System.exit(0);
        }

    }

    /**
     * 获取insert语句
     */
    @Test
    public void createInsertSql() throws IOException {
        String version = backReq.version();
        String fileName = "insert_" + version + ".sql";
        String path_ = java.net.URLDecoder.decode(this.getClass().getResource("/").getPath(),"utf-8");
        String path = path_.substring(0, path_.indexOf("/target/") + 1) + "src/sqlBack/";

        File filePath = new File(path);
        if (!filePath.exists()) {
            filePath.mkdirs();
        }

        File file = new File(path + fileName);

        if (file.exists()) {
            file.delete();
        }

        file.createNewFile();

        FileWriter writer = null;
        try {
            // 打开一个写文件器，构造函数中的第二个参数true表示以追加形式写文件
            writer = new FileWriter(file, true);

            GetInsertSql getInsertSql = new GetInsertSql();

            List<Map<String, Object>> tableList = backReq.tables(Arrays.asList(insertTableName));
            logger.debug("执行ｓｑｌ：" + tableList);
            StringBuffer str = new StringBuffer();
            for (int j = 0; j < tableList.size(); j++) {
                Map<String, Object> map = tableList.get(j);
                String table_name = StringHelper.get(map, "TABLE_NAME");
                if (table_name.endsWith("_his")) {
                    continue;
                }
                String str1 = getInsertSql.getSql(table_name);
                if (!"".equals(str1)) {
                    writer.write(str1 + "\r\n");
                }
            }
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                if (writer != null) {
                    writer.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    private void createFunctionSql(String[] tableName, File file) {

        List<Map<String, Object>> tableList = backReq.procs(Arrays.asList(tableName));
        logger.debug("执行ｓｑｌ：" + tableList);
        StringBuffer str = new StringBuffer();
        for (int j = 0; j < tableList.size(); j++) {
            Map<String, Object> map = tableList.get(j);
            String table_name = StringHelper.get(map, "name");
            String type = StringHelper.get(map, "type");
            insertTableSql(fgf, file);
            if("PROCEDURE".equals(type)){
                insertTableCommet(table_name, 2, file);
            }else{
                insertTableCommet(table_name, 3, file);
            }
        }
    }

    private void createTableSql(String[] tableName, File file) {
//        logger.debug("tableName:"+(tableName.length));
        List<Map<String, Object>> tableList = backReq.tables(Arrays.asList(tableName));
        logger.debug("执行ｓｑｌ：" + tableList);
        StringBuffer str = new StringBuffer();
        for (int j = 0; j < tableList.size(); j++) {
            Map<String, Object> map = tableList.get(j);
            String table_con = StringHelper.get(map, "TABLE_COMMENT");
            String table_name = StringHelper.get(map, "TABLE_NAME");
            if (table_name.endsWith("_his")) {
                continue;
            }
            insertTableSql(fgf, file);
            insertTableSql("##" + table_con, file);
            insertTableCommet(table_name, 1, file);

        }
    }

    /**
     * 获取文件大小
     *
     * @param f
     * @return
     */
    private long getFileSize(File f) {
        FileChannel fc = null;
        try {
            if (f.exists() && f.isFile()) {
                FileInputStream fis = new FileInputStream(f);
                fc = fis.getChannel();
                return fc.size();
            } else {
                logger.info("file doesn't exist or is not a file");
            }
        } catch (FileNotFoundException e) {
            return 0;
        } catch (IOException e) {
            return 0;
        } finally {
            if (null != fc) {
                try {
                    fc.close();
                } catch (IOException e) {
                }
            }
        }
        return 0;
    }

    private void insertTableCommet(String tableName, int type, File file) {
        try{
            insertTableSql(getCreateTableSql(tableName, type), file);
        }catch (Exception e){
            logger.error("",e);
        }

    }

    /**
     * 插入数据到文件中
     *
     * @param file
     */
    private void insertTableSql(String str, File file) {

        FileWriter writer = null;
        try {
            // 打开一个写文件器，构造函数中的第二个参数true表示以追加形式写文件
            writer = new FileWriter(file, true);
            writer.write(str + "\r\n");
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                if (writer != null) {
                    writer.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }

    }

    /**
     *
     * @param tableName
     * @param type 类型　1table 2PROCEDURE 3FUNCTION
     * @return
     */
    private String getCreateTableSql(String tableName, int type) {
        String sql_ = "";
        String sql = "";
        switch (type) {
            case 1:
                Map<String, Object> map = backReq.showTable(tableName);
                sql_ = StringHelper.get(map, "Create Table");
                break;
            case 2:
                map = backReq.showPROCEDURE(tableName);
                sql_ = StringHelper.get(map, "Create Procdedure");
                break;
            case 3:
                map = backReq.showFUNCTION(tableName);
                sql_ = StringHelper.get(map, "Create Function");
                break;
        }
        return sql_ + ";";
    }

    /**
     * 获取表中数据信息，拼接成insert 语句
     */
    public class GetInsertSql {

        public String getSql(String tableName) {
            //获取表结构
            List<Map<String, Object>> list = backReq.showColom(tableName);
            String insertsql = "insert into `" + tableName + "` (";
            for (int i = 0; i < list.size(); i++) {
                String column_name = StringHelper.get(list.get(i), "COLUMN_NAME");
                if (i == list.size() - 1) {
                    insertsql += "`"+column_name+"`";
                } else {
                    insertsql += "`"+column_name + "`,";
                }
            }
            insertsql += ")values";

            //获取表数据信息
            List<Map<String, Object>> listData = backReq.getData(tableName);

            if (listData.isEmpty()) {
                return "";
            }

            for (int i = 0; i < listData.size(); i++) {
                Map<String, Object> map = listData.get(i);
                insertsql += "(";
                for (int j = 0; j < list.size(); j++) {
                    String column_name = StringHelper.get(list.get(j), "COLUMN_NAME");
                    if (j == list.size() - 1) {
                        if ("".equals(StringHelper.get(map, column_name))) {
                            insertsql += "null)";
                        } else {
                            insertsql += "'" + replaceBlank(StringHelper.get(map, column_name)) + "')";
                        }
                    } else {
                        if ("".equals(StringHelper.get(map, column_name))) {
                            insertsql += "null,";
                        } else {
                            insertsql += "'" + replaceBlank(StringHelper.get(map, column_name)) + "',";
                        }
                    }
                }
                if (i != listData.size() - 1) {
                    insertsql += ",";
                }
            }
            return insertsql + ";";
        }

        public String replaceBlank(String str) {
            String dest = "";
            if (str != null) {
                Pattern p = Pattern.compile("\\s*|\t|\r|\n");
                Matcher m = p.matcher(str);
                dest = m.replaceAll("");
            }
            return dest;
        }

    }

}
